﻿using System;
using System.Collections.Generic;
using TeamTasker.Contracts;
using Spring.Data;
using System.Data;
using Spring.Data.Common;
using TeamTasker.Server.DataAccess.Support;
using TeamTasker.Utils;

namespace TeamTasker.Server.DataAccess
{
    public class TeamDAO : BaseDAO<Team>
    {
        public UserDAO UserDAO { get; set; }

        public override Team Load(long id)
        {
            IDbParameters param = AdoTemplate.CreateDbParameters();
            param.Add("TeamID", SqlDbType.BigInt).Value = id;
            Team team = (Team)AdoTemplate.QueryWithResultSetExtractor(CommandType.Text,
                "select ID, Name, CreationDate from Team where ID = @TeamID",
                new TeamResultSetExtractor(),
                param);
            if (team != null)
            {
                IList<TeamMember> members = LoadMembers(team.ID);
                foreach (TeamMember member in members)
                {
                    team.Members.Add(member);
                }
            }
            return team;
        }

        public override void Save(Team entity)
        {
            if (entity.ID == 0)
            {
                CreateTeam(entity);
            }
            else
            {
                UpdateTeam(entity);
            }
        }

        public void UpdateMembers(long teamID,
                                  IList<TeamMember> membersToAdd,
                                  IList<TeamMember> membersToUpdate,
                                  IList<TeamMember> membersToDelete)
        {
            AddTeamMembers(teamID, membersToAdd);
            UpdateTeamMembers(teamID, membersToUpdate);
            DeleteTeamMembers(teamID, membersToDelete);
        }
        
        protected void CreateTeam(Team team) 
        {
            Validate.True(0 == team.ID, "Wrong id, zero is expected");
            
            IDbParameters param = AdoTemplate.CreateDbParameters();
            param.Add("ID", SqlDbType.BigInt).Value = team.ID = SeqGen.GetNextID();
            param.Add("Name", SqlDbType.NVarChar).Value = team.Name;
            param.Add("CreationDate", SqlDbType.DateTime).Value = team.CreationDate;

            AdoTemplate.ExecuteNonQuery(CommandType.Text,
                "insert into Team(ID, Name, CreationDate) values(@ID, @Name, @CreationDate)",
                param);

            InsertTeamMembers(team);
        }
        
        protected void UpdateTeam(Team team)
        {
            Validate.Positive(team.ID, "Wrong id, positive is expected");

            IDbParameters param = AdoTemplate.CreateDbParameters();
            param.Add("Name", SqlDbType.NVarChar).Value = team.Name;
            param.Add("ID", SqlDbType.BigInt).Value = team.ID;

            AdoTemplate.ExecuteNonQuery(CommandType.Text,
                "update Team set Name = @Name where ID = @ID",
                param);

            UpdateTeamMembers(team);
        }

        protected void InsertTeamMembers(Team team)
        {
            AddTeamMembers(team.ID, team.Members);
        }

        protected void AddTeamMembers(long teamID, IList<TeamMember> members)
        {
            foreach (TeamMember member in members)
            {
                member.TeamID = teamID;
                AddTeamMember(member);
            }
        }
       
        protected void UpdateTeamMembers(Team team)
        {
            // TODO: implement analysis and proper update instead of delete/insert
            DeleteTeamMembers(team.ID);
            InsertTeamMembers(team);
        }

        protected void UpdateTeamMembers(long teamID, IList<TeamMember> members)
        {
            foreach (TeamMember member in members)
            {
                member.TeamID = teamID;
                UpdateTeamMember(member);
            }
        }

        protected void DeleteTeamMembers(long teamID)
        {
            IDbParameters param = AdoTemplate.CreateDbParameters();
            param.Add("TeamID", SqlDbType.BigInt).Value = teamID;

            AdoTemplate.ExecuteNonQuery(CommandType.Text,
                "delete from TeamMember where TeamID = @TeamID",
                param);
        }

        protected void DeleteTeamMembers(long teamID, IList<TeamMember> members)
        {
            foreach (TeamMember member in members)
            {
                member.TeamID = teamID;
                DeleteTeamMember(member);
            }
        }

        protected void AddTeamMember(TeamMember member)
        {
            IDbParameters param = AdoTemplate.CreateDbParameters();
            param.Add("TeamID", SqlDbType.BigInt).Value = member.TeamID;
            param.Add("UserID", SqlDbType.BigInt).Value = member.UserID;
            param.Add("IsOwner", SqlDbType.Bit).Value = member.IsOwner;

            AdoTemplate.ExecuteNonQuery(CommandType.Text,
                "insert into TeamMember(TeamID, UserID, IsOwner) values(@TeamID, @UserID, @IsOwner)",
                param);
        }

        protected void UpdateTeamMember(TeamMember member)
        {
            IDbParameters param = AdoTemplate.CreateDbParameters();
            param.Add("TeamID", SqlDbType.BigInt).Value = member.TeamID;
            param.Add("UserID", SqlDbType.BigInt).Value = member.UserID;
            param.Add("IsOwner", SqlDbType.Bit).Value = member.IsOwner;

            AdoTemplate.ExecuteNonQuery(CommandType.Text,
                "update TeamMember set IsOwner = @IsOwner where TeamID = @TeamID and UserID = @UserID",
                param);
        }

        protected void DeleteTeamMember(TeamMember member)
        {
            IDbParameters param = AdoTemplate.CreateDbParameters();
            param.Add("TeamID", SqlDbType.BigInt).Value = member.TeamID;
            param.Add("UserID", SqlDbType.BigInt).Value = member.UserID;

            AdoTemplate.ExecuteNonQuery(CommandType.Text,
                "delete from TeamMember where TeamID = @TeamID and UserID = @UserID",
                param);
        }

        public override void Delete(long id)
        {
            DeleteTeamMembers(id);

            IDbParameters param = AdoTemplate.CreateDbParameters();
            param.Add("ID", SqlDbType.BigInt).Value = id;

            AdoTemplate.ExecuteNonQuery(CommandType.Text,
                "delete from Team where ID = @ID",
                param);
        }

        public IList<long> GetTeamIDs(long userID)
        {
            IDbParameters param = AdoTemplate.CreateDbParameters();
            param.Add("UserID", SqlDbType.BigInt).Value = userID;
            return (IList<long>) AdoTemplate.QueryWithResultSetExtractor(
                CommandType.Text,
                "select TeamID from TeamMember where UserID = @UserID",
                new IdListResultSetExtractor(),
                param);
        }

        protected IList<TeamMember> LoadMembers(long teamID)
        {
            IDbParameters param = AdoTemplate.CreateDbParameters();
            param.Add("TeamID", SqlDbType.BigInt).Value = teamID;
            IList<TeamMember> members = (IList<TeamMember>)AdoTemplate.QueryWithResultSetExtractor(
                CommandType.Text,
                "select UserID, IsOwner from TeamMember where TeamID = @TeamID",
                new TeamMemberListResultSetExtractor(),
                param);
            foreach (TeamMember member in members)
            {
                Validate.Positive(member.UserID, "Team member userID is to be positive");
                member.TeamID = teamID;
                member.User = UserDAO.Load(member.UserID);
            }
            return members;
        }
    }

    class TeamResultSetExtractor : IResultSetExtractor
    {
        public object ExtractData(IDataReader reader)
        {
            if (reader.Read())
            {
                Team team = new Team();
                team.ID = reader.GetInt64(0);
                team.Name = reader.GetString(1);
                team.CreationDate = reader.IsDBNull(2) ? DateTime.MaxValue : reader.GetDateTime(2);
                return team;
            }
            return null;
        }
    }

    class TeamMemberListResultSetExtractor : IResultSetExtractor
    {
        public object ExtractData(IDataReader reader)
        {
            IList<TeamMember> members = new List<TeamMember>();
            while (reader.Read())
            {
                TeamMember member = new TeamMember();
                member.UserID = reader.GetInt64(0);
                member.IsOwner = reader.IsDBNull(1) ? false : reader.GetBoolean(1);
                members.Add(member);
            }
            return members;
        }
    }
    
    class TeamListResultSetExtractor : IResultSetExtractor
    {
        public object ExtractData(IDataReader reader)
        {
            IList<Team> teams = new List<Team>();
            while (reader.Read())
            {
                Team team = new Team();
                team.ID = reader.GetInt64(0);
                team.Name = reader.GetString(1);
                teams.Add(team);
            }
            return teams;
        }
    }
}
